import os
import sqlite3
import sys
from datetime import datetime

from faker import Faker

# 添加项目根目录到Python路径
project_root = os.path.dirname(os.path.dirname(__file__))
sys.path.append(project_root)

# 初始化Faker
fake = Faker("zh_CN")


def generate_test_data():
    """为所有表生成测试数据"""
    # 连接数据库
    db_path = os.path.join(project_root, "data", "hitsz-arch-management.db")
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    try:
        # 1. 生成应届毕业生数据 (20000条)
        print("正在生成应届毕业生数据...")
        for i in range(20000):
            # 生成随机的archive_delivery_status
            archive_delivery_status = fake.random_element(["已寄出", "未寄出"])
            tracking_number = (
                fake.numerify("############")
                if archive_delivery_status == "已寄出"
                else ""
            )

            cursor.execute(
                """
            INSERT OR IGNORE INTO fresh_graduate_record VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
            """,
                (
                    f"fresh_{i + 1:06d}",  # id
                    fake.name(),  # name
                    fake.random_element(
                        [
                            "计算机科学与技术",
                            "电子信息工程",
                            "机械设计制造及其自动化",
                            "工商管理",
                            "会计学",
                        ]
                    ),  # major
                    fake.random_element(["2020", "2021", "2022", "2023"]),  # cohort
                    fake.date_time_between(
                        start_date="-2y", end_date="now"
                    ),  # send_time
                    fake.company(),  # employer
                    fake.address(),  # employer_address
                    fake.province(),  # student_origin_address
                    fake.ssn(),  # student_id_card_number
                    fake.phone_number(),  # student_phone_number
                    fake.random_element(["机要", "EMS", "自提"]),  # archives_recv_type
                    fake.numerify("##########"),  # archives_recv_number
                    fake.company(),  # archives_recv_unit_name
                    fake.address(),  # archives_recv_address
                    fake.name(),  # archives_recvier
                    fake.phone_number(),  # archives_recvier_phone_number
                    fake.random_element(["本科", "硕士", "博士"]),  # education_level
                    # 已寄出时，tracking_number不能为空
                    archive_delivery_status,  # archive_delivery_status
                    tracking_number,  # tracking_number
                    fake.sentence(nb_words=10, variable_nb_words=True),  # remark
                    datetime.now(),  # last_modified_time
                    datetime.now(),  # create_time
                ),
            )

        # 2. 生成往届毕业生数据 (20000条)
        print("正在生成往届毕业生数据...")
        for i in range(20000):
            # 生成随机的archive_delivery_status
            archive_delivery_status = fake.random_element(["已寄出", "未寄出"])
            tracking_number = (
                fake.numerify("############")
                if archive_delivery_status == "已寄出"
                else ""
            )

            cursor.execute(
                """
            INSERT OR IGNORE INTO former_graduate_record VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
            """,
                (
                    f"former_{i + 1:06d}",  # id
                    fake.name(),  # name
                    fake.random_element(
                        [
                            "计算机科学与技术",
                            "电子信息工程",
                            "机械设计制造及其自动化",
                            "工商管理",
                        ]
                    ),  # major
                    fake.date_time_between(
                        start_date="-2y", end_date="now"
                    ),  # send_time
                    fake.company(),  # archives_recv_unit_name
                    fake.address(),  # archives_recv_address
                    fake.name(),  # archives_recvier
                    fake.phone_number(),  # archives_recvier_phone_number
                    fake.random_element(["本科", "硕士", "博士"]),  # education_level
                    # 已寄出时，tracking_number不能为空
                    archive_delivery_status,  # archive_delivery_status
                    tracking_number,  # tracking_number
                    fake.sentence(nb_words=10, variable_nb_words=True),  # remark
                    datetime.now(),  # last_modified_time
                    datetime.now(),  # create_time
                ),
            )

        # 3. 生成硕士数据 (20000条)
        print("正在生成硕士数据...")
        for i in range(20000):
            cursor.execute(
                """
            INSERT OR IGNORE INTO shuoshi_record VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
            """,
                (
                    f"master_{i + 1:06d}",  # id
                    fake.name(),  # name
                    "硕士",  # education_level (默认值)
                    fake.random_element(["全日制", "非全日制"]),  # learning_mode
                    fake.random_element(
                        [
                            "计算机学院",
                            "电子与信息学院",
                            "机械与汽车工程学院",
                            "管理学院",
                        ]
                    ),  # college
                    fake.random_element(["学术型", "专业型"]),  # student_category
                    fake.random_element(["统考", "推免"]),  # education_mode
                    fake.random_element(["是", "否"]),  # is_hac_student
                    fake.phone_number(),  # phone_number
                    fake.random_element(
                        ["中共党员", "共青团员", "群众"]
                    ),  # political_status
                    fake.ssn(),  # id_card_number
                    fake.random_element(
                        ["毕业", "在读", "肄业", "结业", "退学"]
                    ),  # graduation_status
                    fake.random_element(["已到档", "未到档"]),  # archive_arrvial_status
                    fake.date_time_between(
                        start_date="-3y", end_date="now"
                    ),  # archive_arrvial_time
                    "",  # party_archive_lend_archive_record
                    fake.random_element(
                        ["已寄出", "未寄出"]
                    ),  # archive_delivery_status
                    datetime.now(),  # last_modified_time
                    datetime.now(),  # create_time
                ),
            )

        # 4. 生成博士数据 (20000条)
        print("正在生成博士数据...")
        for i in range(20000):
            cursor.execute(
                """
            INSERT OR IGNORE INTO boshi_record VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
            """,
                (
                    f"phd_{i + 1:06d}",  # id
                    fake.name(),  # name
                    "博士",  # education_level (默认值)
                    fake.random_element(["全日制", "非全日制"]),  # learning_mode
                    fake.random_element(
                        [
                            "计算机学院",
                            "电子与信息学院",
                            "机械与汽车工程学院",
                            "材料科学与工程学院",
                        ]
                    ),  # college
                    fake.random_element(["学术型", "专业型"]),  # student_category
                    fake.random_element(["统考", "推免", "直博"]),  # education_mode
                    fake.random_element(["是", "否"]),  # is_hac_student
                    fake.phone_number(),  # phone_number
                    fake.random_element(
                        ["中共党员", "共青团员", "群众"]
                    ),  # political_status
                    fake.ssn(),  # id_card_number
                    fake.random_element(
                        ["毕业", "在读", "肄业", "结业", "退学"]
                    ),  # graduation_status
                    fake.random_element(["已到档", "未到档"]),  # archive_arrvial_status
                    fake.date_time_between(
                        start_date="-5y", end_date="now"
                    ),  # archive_arrvial_time
                    "",  # party_archive_lend_archive_record
                    fake.random_element(
                        ["已寄出", "未寄出"]
                    ),  # archive_delivery_status
                    datetime.now(),  # last_modified_time
                    datetime.now(),  # create_time
                ),
            )

        # 5. 生成本科数据 (20000条)
        print("正在生成本科数据...")
        for i in range(20000):
            cursor.execute(
                """
            INSERT OR IGNORE INTO benke_record VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
            """,
                (
                    f"bachelor_{i + 1:06d}",  # id
                    fake.name(),  # name
                    "本科",  # education_level (默认值)
                    fake.random_element(["全日制", "非全日制"]),  # learning_mode
                    fake.random_element(
                        [
                            "计算机学院",
                            "电子与信息学院",
                            "机械与汽车工程学院",
                            "管理学院",
                            "外国语学院",
                        ]
                    ),  # college
                    fake.random_element(
                        [
                            "计算机科学与技术",
                            "电子信息工程",
                            "机械设计制造及其自动化",
                            "工商管理",
                            "英语",
                        ]
                    ),  # major
                    f"{fake.random_int(2018, 2023)}级{fake.random_int(1, 4)}班",  # class_name
                    f"{fake.random_int(1, 50):02d}",  # class_number
                    fake.random_element(["是", "否"]),  # is_international_student
                    fake.random_element(["是", "否"]),  # is_hac_student
                    fake.phone_number(),  # phone_number
                    fake.random_element(
                        ["在读", "毕业", "休学", "退学"]
                    ),  # record_status
                    fake.random_element(
                        ["中共党员", "共青团员", "群众"]
                    ),  # political_status
                    fake.ssn(),  # id_card_number
                    fake.random_element(
                        ["毕业", "在读", "肄业", "结业", "退学"]
                    ),  # graduation_status
                    fake.random_element(["已到档", "未到档"]),  # archive_arrvial_status
                    fake.date_time_between(
                        start_date="-4y", end_date="now"
                    ),  # archive_arrvial_time
                    "",  # party_archive_lend_archive_record
                    fake.random_element(
                        ["已寄出", "未寄出"]
                    ),  # archive_delivery_status
                    datetime.now(),  # last_modified_time
                    datetime.now(),  # create_time
                ),
            )

        conn.commit()
        print("测试数据生成完成！共生成100000条数据（每表20000条）")

    except Exception as e:
        print(f"生成测试数据时出错: {e}")
        conn.rollback()
    finally:
        conn.close()


if __name__ == "__main__":
    generate_test_data()
